Delphi Magazine Collection 2001/Delphi Magazine Collection 20001 (2001).iso/DISKS/Issue38/Survive/Demo1.pas

Pascal/Delphi Source File | 1998-08-25 | 6.0 KB | 260 lines

unit Demo1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, DBGrids, DBTables, Db, ExtCtrls, DBCtrls, StdCtrls;

type
  TfrmMain = class(TForm)
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    btnNew: TButton;
    btnSave: TButton;
    btnClose: TButton;
    Label1: TLabel;
    cboQuaID: TComboBox;
    DBGrid2: TDBGrid;
    qrySubset: TQuery;
    dsSubset: TDataSource;
    btnExecuteQuery: TButton;
    Memo1: TMemo;
    btnGenerateQuery: TButton;
    procedure FormCreate(Sender: TObject);
    procedure btnSaveClick(Sender: TObject);
    procedure btnCloseClick(Sender: TObject);
    procedure cboQuaIDClick(Sender: TObject);
    procedure btnNewClick(Sender: TObject);
    procedure DBNavigator1BeforeAction(Sender: TObject;
      Button: TNavigateBtn);
    procedure btnExecuteQueryClick(Sender: TObject);
    procedure btnGenerateQueryClick(Sender: TObject);
  private
  protected
    procedure LoadQualifierSet(aID: Integer);
    procedure PopulateQuaIDDropDown;
    procedure GenerateWhereClause(aQuaID: Integer; aWhereClause: TStrings);
  public
  end;

var
  frmMain: TfrmMain;

implementation

uses dmDemo;

{$R *.DFM}

procedure TfrmMain.LoadQualifierSet(aID: Integer);
begin
  with DemoDM.qryQualifiers do
  begin
    if Active then Close;
    Params[0].AsInteger := aID;
    Open;
  end;
end;

procedure TfrmMain.PopulateQuaIDDropDown;
begin
  with cboQuaID, DemoDM.qryQuaIDLookup do
  begin
    Items.Clear;
    Open;
    First;
    while not Eof do
    begin
      Items.Add(IntToStr(Fields[0].AsInteger));
      Next;
    end;
    Close;
  end;
end;

procedure TfrmMain.FormCreate(Sender: TObject);
begin
  PopulateQuaIDDropDown;
end;

procedure TfrmMain.btnSaveClick(Sender: TObject);
var
  ID: Integer;
  BMark: TBookmark;
begin
  with DemoDM, qryQualifiers do
  begin
    Database.StartTransaction;
    try
      if Trim(cboQuaID.Text) = '' then
        with qryMaxQuaIDLookup do
        begin
          Open;
          ID := Fields[0].AsInteger;
          Close;
        end
      else
        ID := StrToInt(cboQuaID.Text);

      DisableControls;
      BMark := GetBookmark;
      try
        First;
        while not Eof do
        begin
          Edit;
          FieldByName('quaID').AsInteger := ID;
          Post;
          Next;
        end;
      finally
        GotoBookmark(BMark);
        FreeBookmark(BMark);
        EnableControls;
      end;
      ApplyUpdates;
      Database.Commit;
      CommitUpdates;
      PopulateQuaIDDropDown;
    except
      Database.Rollback;
      raise;
    end;
  end;
end;

procedure TfrmMain.btnCloseClick(Sender: TObject);
begin
  Close;
end;

procedure TfrmMain.cboQuaIDClick(Sender: TObject);
begin
  Beep;
  LoadQualifierSet(StrToInt(cboQuaID.Text));
end;

procedure TfrmMain.btnNewClick(Sender: TObject);
begin
  cboQuaID.Text := '';
  LoadQualifierSet(-1);  { force an empty set }
end;

procedure TfrmMain.DBNavigator1BeforeAction(Sender: TObject;
  Button: TNavigateBtn);
begin
  if Button = nbInsert then
  begin
    DemoDM.qryQualifiers.Append;
    Abort;
  end;
end;

procedure TfrmMain.GenerateWhereClause(aQuaID: Integer; aWhereClause: TStrings);
var
  Query: TQuery;
  ValueList: TStringList;
  LastFilterType: Integer;
  I: Integer;
  FilterExpressions: TStringList;

  procedure AddFilter(aValues: TStrings; aFilters: TStrings);
  var
    I: Integer;
    Expr: string;
  begin
    if aValues.Count = 1 then
      aFilters.Add(Format('(%s)', [aValues[0]]))
    else
    begin
      Expr := '';
      for I := 0 to aValues.Count - 1 do
      begin
        Expr := Expr + '(' + aValues[I] + ')';
        if I < aValues.Count - 1 then
          Expr := Expr + ' OR '
      end;
      aFilters.Add(Format('(%s)', [Expr]));
    end;
    aValues.Clear;
  end;
begin
  Query := TQuery.Create(nil);
  ValueList := TStringList.Create;
  FilterExpressions := TStringList.Create;
  try
    with Query do
    begin
      DatabaseName := DemoDM.Database1.DatabaseName;
      SQL.Clear;
      SQL.Add('SELECT *, qlfDataField');
      SQL.Add('  FROM Qualifiers, QualifierFilters');
      SQL.Add('  WHERE quaFilterID = qlfID AND');
      SQL.Add('        quaID = ' + IntToStr(aQuaID));
      SQL.Add('  ORDER BY quaFilterID');
      Open;
      try

        { Convert the qualifiers to expressions for the WHERE clause }
        LastFilterType := -1;
        while not Eof do
        begin

          { There may be duplicates on filter ID which means we OR the values for the
            same field }
          if (LastFilterType <> -1) and (LastFilterType <> FieldByName('quaFilterID').AsInteger) then
            AddFilter(ValueList, FilterExpressions);

          LastFilterType := FieldByName('quaFilterID').AsInteger;
          ValueList.Add(Format('%s = "%s"',
                               [FieldByName('qlfDataField').AsString,
                                FieldByName('quaCode').AsString]));

          Next;
        end;
        AddFilter(ValueList, FilterExpressions);
      finally
        Close;
      end;

      { Build the WHERE clause }
      with aWhereClause do
      begin
        Add('  WHERE ');
        for I := 0 to FilterExpressions.Count - 1 do
        begin
          if I < FilterExpressions.Count - 1 then
            Add('    ' + FilterExpressions[I] + ' AND')
          else
            Add('    ' + FilterExpressions[I]);
        end;
      end;
    end;
  finally
    Query.Free;
    ValueList.Free;
    FilterExpressions.Free;
  end;
end;

procedure TfrmMain.btnExecuteQueryClick(Sender: TObject);
begin
  qrySubset.Open;
end;

procedure TfrmMain.btnGenerateQueryClick(Sender: TObject);
begin
  with qrySubset do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT * FROM EmpComp');
    GenerateWhereClause(StrToInt(cboQuaID.Text), SQL);
    Memo1.Lines.Clear;
    Memo1.Lines.AddStrings(SQL);
  end;
end;

end.